

	
alter   function fn_backlog_report_for_vertical_report(
	
	 
	
@product_Id int,
@customer_Id varchar(20),
@quarter varchar(10),
@year  varchar(10),
@Backlog_Type varchar(20),
@defaultPrompt varchar(50),
@category varchar(15),
@CURRENT_DATE datetime )
	
returns @fn_results Table(Amount float, status varchar(20), orderDate datetime) 

as
begin
	 
	declare @results table(Amount float, status varchar(20), orderDate datetime) 
	 
	 declare @part_Number varchar(50)
	 declare @customer_Name varchar(65)
	 declare @schedule_Date datetime
	 declare @amount float
	 declare @status varchar(50)
	 declare @date_From varchar(10)
	 declare @date_To varchar(10)
	 declare @dateDiff datetime
	
	 
	
	 declare @month varchar(10)
	 declare @sales_rep varchar(50)
	 declare @end_user varchar(50)
	
	 
	
	 declare @LineItem_ID int
	 declare @Unit_Price float 
	 declare @Shipped_Qty int
	 declare @Shipped_Amount float
	  
	 declare @revised_date datetime 
	 declare @requested_date datetime 
	
	 declare @req_date datetime
	
	 if @Backlog_Type = 'all'
	begin
		set @backlog_Type = '0'
	end
	
	 
	 set @month = '1' 
	 
	
	 set @date_From = CONVERT(varchar,CONVERT(datetime,@month+'/01/'+@year),101)
	 set @date_To   = CONVERT(varchar,DATEADD(month,12,CONVERT(datetime,@date_From)),101)
	 set @dateDiff  = CONVERT(datetime,DATEDIFF(day,1,@date_To),101)
	 set @date_To   = CONVERT(varchar,@dateDiff,101)
	
	
	 declare @CoolCursor CURSOR
	 
	
	 if (@product_Id <> '0' and @customer_Id <> '0' )
	 begin
	  
	 
		  -- Added by gandhi on 30th Oct 2004		
		  -- Commented by gandhi on 7th Dec 2004	
			/*select @req_date = COOL.COOL_Requested_Date
				from product,
		                     Customer_Open_Order_LineItem COOL,
				     Sales_Order,Customer 
	
				where 
				COOL.COOL_Requested_Date>=@date_From
				and COOL.COOL_Requested_Date<=@date_To
				and COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.SO_Is_Sample = 1
				and Sales_Order.Customer_ID=customer.Customer_ID
		                and COOL_Status <> 'SHIPPED'*/		
			-- Ends here - Gandhi	
	
	  if (@category = 'REQUESTED')                
	         begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
	   from product, 
	        Customer_Open_Order_LineItem COOL, 
	        Sales_Order,Customer 
	
	 
	
	   where 
	   --COOL.COOL_Requested_Date>=@date_From
	   --and COOL.COOL_Requested_Date<=@date_To
	   --and
	   COOL.Product_ID=product.Product_ID 
	   and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
	   and Sales_Order.Customer_ID=customer.Customer_ID
	   and Sales_Order.SO_Is_Sample = 1	
	   and COOL_Status <> 'SHIPPED'
	   and customer.Customer_ID= @customer_Id 
	   and COOL.Product_ID= @product_Id
	   and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
           or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))		
	  end
	  else if (@category = 'SCHEDULE')
	  begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser,COOL.COOL_Requested_Date
	   from product, 
	        Customer_Open_Order_LineItem COOL, 
	        Sales_Order,Customer 
	
	 
	
	   where 
	   COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and COOL.Product_ID= @product_Id
				and customer.Customer_ID= @customer_Id
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	 end
	 else if (@product_Id = '0' and @customer_Id = '0'  )
	 begin
	
	  -- Added by gandhi on 30th Oct 2004		
			/*select @req_date = COOL.COOL_Requested_Date
				from product,
		                     Customer_Open_Order_LineItem COOL,
				     Sales_Order,Customer 
	
				where 
				COOL.COOL_Requested_Date>=@date_From
				and COOL.COOL_Requested_Date<=@date_To
				and COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.SO_Is_Sample = 1
				and Sales_Order.Customer_ID=customer.Customer_ID
		                and COOL_Status <> 'SHIPPED'*/		
			-- Ends here - Gandhi
	                 
	  if (@category = 'REQUESTED')                
	         begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
	   from product,
	                      Customer_Open_Order_LineItem COOL,
	        Sales_Order,Customer 
	
	 
	
	   where 
	   --COOL.COOL_Requested_Date>=@date_From
	   --and COOL.COOL_Requested_Date<=@date_To
	   --and
	   COOL.Product_ID=product.Product_ID 
	   and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
	   and Sales_Order.Customer_ID=customer.Customer_ID
	   and Sales_Order.SO_Is_Sample = 1
	   and COOL_Status <> 'SHIPPED'
	                 --and customer.Customer_ID= @customer_Id 
	   --and COOL.Product_ID= @product_Id
	   and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
           or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	  else if (@category = 'SCHEDULE')
	  begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser,COOL.COOL_Requested_Date
	   from product,
	                      Customer_Open_Order_LineItem COOL,
	        Sales_Order,Customer 
	
	 
	
	   where 
	   COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	  
	                
	 end
	 if (@product_Id <> '0' and @customer_Id = '0'  )
	 begin
	   
	  -- Added by gandhi on 30th Oct 2004		
			/*select @req_date = COOL.COOL_Requested_Date
				from product,
		                     Customer_Open_Order_LineItem COOL,
				     Sales_Order,Customer 
	
				where 
				COOL.COOL_Requested_Date>=@date_From
				and COOL.COOL_Requested_Date<=@date_To
				and COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.SO_Is_Sample = 1
				and Sales_Order.Customer_ID=customer.Customer_ID
		                and COOL_Status <> 'SHIPPED'*/		
			-- Ends here - Gandhi
	
	  if (@category = 'REQUESTED')                
	         begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
	   from product,
	    Customer_Open_Order_LineItem COOL,
	    Sales_Order,Customer 
	   where 
	   --COOL.COOL_Requested_Date>=@date_From
	   --and COOL.COOL_Requested_Date<=@date_To
	   --and
	   COOL.Product_ID=product.Product_ID 
	   and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
	   and Sales_Order.Customer_ID=customer.Customer_ID
	   and Sales_Order.SO_Is_Sample = 1
	   and COOL_Status <> 'SHIPPED'
           --and customer.Customer_ID= @customer_Id 
	   and COOL.Product_ID= @product_Id
	   and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
           or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	  else if (@category = 'SCHEDULE')
	  begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser,COOL.COOL_Requested_Date
	   from product,
	    Customer_Open_Order_LineItem COOL,
	    Sales_Order,Customer 
	   where 
	   COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and COOL.Product_ID= @product_Id
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	
	 
	
	 end
	 if (@product_Id = '0' and @customer_Id <> '0'  )
	 begin
	  
	   -- Added by gandhi on 30th Oct 2004		
			/*select @req_date = COOL.COOL_Requested_Date
				from product,
		                     Customer_Open_Order_LineItem COOL,
				     Sales_Order,Customer 
	
				where 
				COOL.COOL_Requested_Date>=@date_From
				and COOL.COOL_Requested_Date<=@date_To
				and COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.SO_Is_Sample = 1
				and Sales_Order.Customer_ID=customer.Customer_ID
		                and COOL_Status <> 'SHIPPED'*/		
			-- Ends here - Gandhi
	
	   if (@category = 'REQUESTED')                
	   begin
	   set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
	   from product,
	    Customer_Open_Order_LineItem COOL,
	    Sales_Order,Customer 
	   where 
	   --COOL.COOL_Requested_Date>=@date_From
	   --and COOL.COOL_Requested_Date<=@date_To
	   --and
           COOL.Product_ID=product.Product_ID 
	   and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID
	   and Sales_Order.Customer_ID=customer.Customer_ID
	   and Sales_Order.SO_Is_Sample = 1	
	   and COOL_Status <> 'SHIPPED'
	   and customer.Customer_ID= @customer_Id 
	   --and COOL.Product_ID= @product_Id
	   and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
           or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))	
	  end
	  else if (@category = 'SCHEDULE')
	  begin
	      set @CoolCursor = CURSOR for
	   select product.Part_Number,
	          customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser,COOL.COOL_Requested_Date
	   from product,
	    Customer_Open_Order_LineItem COOL,
	    Sales_Order,Customer 
	   where 
	   COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and customer.Customer_ID= @customer_Id 
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	  end
	 end
	
	 
	 
	
	
	open @CoolCursor 
		-- Modified by Gandhi on 7th Dec 2004 
		if (@category = 'REQUESTED') 
		begin
			  fetch next from @CoolCursor
			  into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
		end
		else if (@category = 'SCHEDULE')
		begin
			fetch next from @CoolCursor
			  into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date
		end
		-- Ends here - Gandhi 
	  while @@fetch_status = 0
	  begin
		    
		   if(@amount is null)
		   begin
		    set @amount = 0
		   end
		
		 	
		   /*if(@schedule_Date is null OR @schedule_Date= '')
		   begin
		      set @schedule_Date = @req_date
		   end
		  
		   if (@category = 'SCHEDULE')
		   begin
		    if(@revised_date is not null OR @revised_date <> '')
		    begin
		     set @schedule_date = @revised_date
		    end
		   end*/
		
		   if (@category = 'REQUESTED')                
        		begin
				
				if( @schedule_Date < @CURRENT_DATE )
				begin
					set @schedule_Date = @CURRENT_DATE
				end
				else
				begin
					set @schedule_Date = @schedule_Date
				end
			end	
	
			if (@category = 'SCHEDULE')
			begin
				if(@schedule_Date is null OR @schedule_Date= '')
				begin
					
					set @schedule_Date = @CURRENT_DATE
				end
				else
				begin
					set @schedule_Date = @schedule_Date

					if( @schedule_Date < @CURRENT_DATE )
					begin
						set @schedule_Date = @CURRENT_DATE
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
			
			if (@category = 'SCHEDULE')
			begin
				if(@revised_date is not null OR @revised_date <> '')
				begin
					set @schedule_Date = @revised_date
					
					if( @schedule_Date < @CURRENT_DATE )
					begin
						set @schedule_Date = @CURRENT_DATE
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end

					
				end
			end

		
		   select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
		   
		   set @amount = @amount - (@Unit_Price * @Shipped_Qty) 
		
		 
		
		   if ( @amount < 0 )
		   begin
		    set @amount = 0 
		   end
		
		 
		
		   insert into @results(Amount, status, orderDate ) values( @amount, @status, @schedule_Date )  
		
		 
		
		   set @part_number = null
		   set @customer_name = null
		   set @schedule_date = null
		   set @amount = null
		   set @status = null
		   set @LineItem_ID = null
		   set @unit_price = null
		   set @requested_date = null
		   set @revised_date = null
		
		-- Modified by Gandhi on 7th Dec 2004 
		if (@category = 'REQUESTED') 
		begin
			  fetch next from @CoolCursor
			  into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
		end
		else if (@category = 'SCHEDULE')
		begin
			fetch next from @CoolCursor
			  into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date
		end	
		-- Ends here - Gandhi
	end

	 close @CoolCursor
	 deallocate @CoolCursor
		
		 
		if @Backlog_Type <> '0' 
			begin
				
				if (@Category = 'REQUESTED')
				begin
					insert @fn_results select * from @results where status = @Backlog_Type --order by Req_Date
					return
				end
				else if (@Category = 'SCHEDULE')
				begin
					insert @fn_results select * from @results where status = @Backlog_Type --order by Schd_Date
					return
				end
				/*else if (@Category = 'revDate')
				begin
					insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Rev_Date
					return
				end
				else if (@Category = 'salesOrder')
				begin
					insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Sales_Order_Number
					return
				end*/
				
			end
			else
			begin
				
				
				if (@Category = 'REQUESTED')
				begin
					insert @fn_results select * from @results --order by Req_Date
					return
				end
				else if (@Category = 'SCHEDULE')
				begin
					insert @fn_results select * from @results --order by Schd_Date
					return
				end
				/*else if (@Category = 'revDate')
				begin
					insert @fn_results select * from @results order by Rev_Date
					return
				end
				else if (@Category = 'salesOrder')
				begin
					insert @fn_results select * from @results order by Sales_Order_Number
					return
				end*/
			end
	
return 
end
	
	
	
